﻿-------------------------------------------------
-- Export file for user BILLING                --
-- Created by Vladimir on 25.01.2009, 14:15:45 --
-------------------------------------------------

spool db.log

prompt
prompt Creating table CLIENTS
prompt ======================
prompt
create table BILLING.CLIENTS
(
  CLIENT_ID       NUMBER not null,
  ACCOUNT_NUMBER  NUMBER not null,
  MONEY           NUMBER,
  FIO             VARCHAR2(100) not null,
  ADDRESS         VARCHAR2(100) not null,
  PASSPORT        NUMBER not null,
  CONCLUSION_DATE DATE
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table BILLING.CLIENTS
  add primary key (CLIENT_ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt
prompt Creating table NUMBERS
prompt ======================
prompt
create table BILLING.NUMBERS
(
  NUMBER_ID NUMBER not null,
  NUM       NUMBER not null,
  ADDRESS   VARCHAR2(200),
  TARIFF_ID NUMBER not null,
  CLIENT_ID NUMBER not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table BILLING.NUMBERS
  add primary key (NUMBER_ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table BILLING.NUMBERS
  add constraint ALTER_KEY1 unique (NUM)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table BILLING.NUMBERS
  add constraint NUMBERS_CON foreign key (CLIENT_ID)
  references BILLING.CLIENTS (CLIENT_ID) on delete cascade;

prompt
prompt Creating table CONNECTIONS
prompt ==========================
prompt
create table BILLING.CONNECTIONS
(
  CONNECTION_ID   NUMBER not null,
  NUMBER_ID       NUMBER not null,
  TARGET_NUMBER   NUMBER not null,
  CONNECTION_DATE DATE,
  DURATION        NUMBER,
  TARGET_PREFIX   NUMBER not null,
  COST            NUMBER not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table BILLING.CONNECTIONS
  add primary key (CONNECTION_ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table BILLING.CONNECTIONS
  add constraint CONNECTIONS_CON foreign key (NUMBER_ID)
  references BILLING.NUMBERS (NUMBER_ID) on delete cascade;

prompt
prompt Creating table DIRECTIONS
prompt =========================
prompt
create table BILLING.DIRECTIONS
(
  DIRECTION_ID NUMBER not null,
  NAME         VARCHAR2(100) not null,
  PREFIX       NUMBER not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table BILLING.DIRECTIONS
  add primary key (DIRECTION_ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table BILLING.DIRECTIONS
  add constraint DIRECTIONS_CON unique (PREFIX)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt
prompt Creating table OPERATIONS
prompt =========================
prompt
create table BILLING.OPERATIONS
(
  OPERATION_ID   NUMBER not null,
  OPERATION_DATE DATE not null,
  AMOUNT         NUMBER not null,
  CLIENT_ID      NUMBER not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table BILLING.OPERATIONS
  add primary key (OPERATION_ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table BILLING.OPERATIONS
  add foreign key (CLIENT_ID)
  references BILLING.CLIENTS (CLIENT_ID);

prompt
prompt Creating table TARIFFS
prompt ======================
prompt
create table BILLING.TARIFFS
(
  TARIFF_ID NUMBER not null,
  NAME      VARCHAR2(100) not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table BILLING.TARIFFS
  add primary key (TARIFF_ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt
prompt Creating table RATE_SCHEDULE
prompt ============================
prompt
create table BILLING.RATE_SCHEDULE
(
  TARIFF_ID    NUMBER not null,
  DIRECTION_ID NUMBER not null,
  PRICE        NUMBER
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table BILLING.RATE_SCHEDULE
  add constraint RATE_SCHEDULE_CON foreign key (TARIFF_ID)
  references BILLING.TARIFFS (TARIFF_ID) on delete cascade;

prompt
prompt Creating sequence RECALCULATIONS_SEQ
prompt ====================================
prompt
create sequence BILLING.RECALCULATIONS_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 123
increment by 1
cache 20;

prompt
prompt Creating sequence S_CLIENT_ACCOUNT
prompt ==================================
prompt
create sequence BILLING.S_CLIENT_ACCOUNT
minvalue 1
maxvalue 999999999999999999999999999
start with 26
increment by 1
nocache;

prompt
prompt Creating sequence S_CLIENT_ID
prompt =============================
prompt
create sequence BILLING.S_CLIENT_ID
minvalue 1
maxvalue 999999999999999999999999999
start with 35
increment by 1
nocache;

prompt
prompt Creating sequence S_CONNECTION_ID
prompt =================================
prompt
create sequence BILLING.S_CONNECTION_ID
minvalue 1
maxvalue 999999999999999999999999999
start with 437
increment by 1
nocache;

prompt
prompt Creating sequence S_DIRECTION_ID
prompt ================================
prompt
create sequence BILLING.S_DIRECTION_ID
minvalue 1
maxvalue 999999999999999999999999999
start with 6
increment by 1
nocache;

prompt
prompt Creating sequence S_NUMBER_ID
prompt =============================
prompt
create sequence BILLING.S_NUMBER_ID
minvalue 1
maxvalue 999999999999999999999999999
start with 20
increment by 1
nocache;

prompt
prompt Creating sequence S_OPERATION_ID
prompt ================================
prompt
create sequence BILLING.S_OPERATION_ID
minvalue 1
maxvalue 999999999999999999999999999
start with 30
increment by 1
nocache;

prompt
prompt Creating sequence S_TARIFF_ID
prompt =============================
prompt
create sequence BILLING.S_TARIFF_ID
minvalue 1
maxvalue 999999999999999999999999999
start with 14
increment by 1
nocache;

prompt
prompt Creating function GETALL
prompt ========================
prompt
create or replace function billing."GETALL" (c_id IN NUMBER) return NUMBER
IS CON NUMBER := 0; 
   OP  NUMBER := 0; 
begin 
SELECT SUM(C."COST") 
INTO CON 
FROM NUMBERS N, CONNECTIONS C 
WHERE N.NUMBER_ID = C.NUMBER_ID AND (N.CLIENT_ID = c_id) 
AND TO_CHAR(SYSDATE, 'YYYYMM') >= TO_CHAR(C.CONNECTION_DATE, 'YYYYMM'); 

SELECT SUM(O."AMOUNT") 
INTO OP  
FROM OPERATIONS O  
WHERE (O.CLIENT_ID = c_id) 
AND TO_CHAR(SYSDATE, 'YYYYMM') >= TO_CHAR(O.OPERATION_DATE, 'YYYYMM'); 

IF (CON IS NULL) THEN CON := 0; END IF;
IF (OP IS NULL) THEN OP := 0; END IF;
RETURN (CON - OP); 
end;
/

prompt
prompt Creating function GETDEBT
prompt =========================
prompt
create or replace function billing."GETDEBT" (c_id IN NUMBER) return NUMBER
IS CON NUMBER := 0; 
   OP  NUMBER := 0; 
begin 
SELECT SUM(C."COST") 
INTO CON 
FROM NUMBERS N, CONNECTIONS C 
WHERE N.NUMBER_ID = C.NUMBER_ID AND (N.CLIENT_ID = c_id) 
AND TO_CHAR(SYSDATE, 'YYYYMM') > TO_CHAR(C.CONNECTION_DATE, 'YYYYMM'); 

SELECT SUM(O."AMOUNT") 
INTO OP  
FROM OPERATIONS O  
WHERE (O.CLIENT_ID = c_id) 
AND TO_CHAR(SYSDATE, 'YYYYMM') > TO_CHAR(O.OPERATION_DATE, 'YYYYMM'); 

IF (CON IS NULL) THEN CON := 0; END IF;
IF (OP IS NULL) THEN OP := 0; END IF;
RETURN (CON - OP); 
end;
/

prompt
prompt Creating function GETOPTHISMONTH
prompt ================================
prompt
create or replace function billing."GETOPTHISMONTH" (c_id IN NUMBER) return NUMBER
IS OP NUMBER := 0; 
begin 

SELECT SUM(O."AMOUNT") 
INTO OP  
FROM OPERATIONS O  
WHERE (O.CLIENT_ID = c_id) 
AND TO_CHAR(SYSDATE, 'YYYYMM') = TO_CHAR(O.OPERATION_DATE, 'YYYYMM'); 

IF (OP IS NULL) THEN OP := 0; END IF;
RETURN OP; 
end;
/

prompt
prompt Creating function GETTHISMONTH
prompt ==============================
prompt
create or replace function billing."GETTHISMONTH" (c_id IN NUMBER) return NUMBER
IS CON NUMBER := 0; 
begin 
SELECT SUM(C."COST") 
INTO CON 
FROM NUMBERS N, CONNECTIONS C 
WHERE N.NUMBER_ID = C.NUMBER_ID AND (N.CLIENT_ID = c_id) 
AND TO_CHAR(SYSDATE, 'YYYYMM') = TO_CHAR(C.CONNECTION_DATE, 'YYYYMM'); 

IF (CON IS NULL) THEN CON := 0; END IF;
RETURN (CON); 
end;
/

prompt
prompt Creating view DRAFT_VIEW
prompt ========================
prompt
CREATE OR REPLACE VIEW BILLING.DRAFT_VIEW AS
SELECT C.CLIENT_ID, C.ACCOUNT_NUMBER,
GETDEBT(C.CLIENT_ID) AS ONE, 
GETTHISMONTH(C.CLIENT_ID) AS TWO,
GETOPTHISMONTH(C.CLIENT_ID) AS THREE, 
GETALL(C.CLIENT_ID) AS FOUR
FROM CLIENTS C
GROUP BY C.CLIENT_ID, C.ACCOUNT_NUMBER;

prompt
prompt Creating view NUMBERS_STATS
prompt ===========================
prompt
CREATE OR REPLACE VIEW BILLING.NUMBERS_STATS AS
SELECT     NUMBERS.NUM, NUMBERS.CLIENT_ID, CLIENTS.ACCOUNT_NUMBER, SUM(CONNECTIONS."COST") AS COST, SUM(CONNECTIONS.DURATION) AS DUR
FROM         NUMBERS, CONNECTIONS, CLIENTS
WHERE     NUMBERS.NUMBER_ID = CONNECTIONS.NUMBER_ID AND CLIENTS.CLIENT_ID = NUMBERS.CLIENT_ID
AND TO_CHAR(SYSDATE, 'YYYYMM') = TO_CHAR(CONNECTIONS.CONNECTION_DATE, 'YYYYMM')
GROUP BY NUMBERS.CLIENT_ID, NUMBERS.NUM, CLIENTS.ACCOUNT_NUMBER;

prompt
prompt Creating view STATISTIC_VIEW
prompt ============================
prompt
CREATE OR REPLACE VIEW BILLING.STATISTIC_VIEW AS
SELECT     C.CONNECTION_ID, C.NUMBER_ID, N.NUM, C.TARGET_NUMBER, C.CONNECTION_DATE, C.DURATION, C.TARGET_PREFIX, C.COST
FROM         CONNECTIONS C, NUMBERS N
WHERE     N.NUMBER_ID = C.NUMBER_ID AND
(C.CONNECTION_DATE >
                          (SELECT     SYSDATE - 30 AS EXPR1
                            FROM          SYS."DUAL"))
ORDER BY C.CONNECTION_DATE;

prompt
prompt Creating procedure PUTMONEY
prompt ===========================
prompt
create or replace procedure billing."PUTMONEY"
(s IN NUMBER,
c_id IN NUMBER)
is
begin
UPDATE CLIENTS SET MONEY = MONEY + s WHERE (CLIENT_ID = c_id); INSERT INTO OPERATIONS ( OPERATION_ID, OPERATION_DATE, AMOUNT, CLIENT_ID ) VALUES ( 0, (SELECT SYSDATE FROM DUAL), s, c_id );
end;
/

prompt
prompt Creating procedure RECOUNT
prompt ==========================
prompt
create or replace procedure billing."RECOUNT"
is
begin
UPDATE    CLIENTS
SET              MONEY =
                          (SELECT     SUM(CONNECTIONS.DURATION * RATE_SCHEDULE.PRICE) AS EXPR1
                            FROM          NUMBERS, CLIENTS CLIENTS_2, CONNECTIONS, TARIFFS, DIRECTIONS, RATE_SCHEDULE
                            WHERE      NUMBERS.CLIENT_ID = CLIENTS_2.CLIENT_ID AND NUMBERS.NUMBER_ID = CONNECTIONS.NUMBER_ID AND
                                                   NUMBERS.TARIFF_ID = TARIFFS.TARIFF_ID AND CONNECTIONS.TARGET_PREFIX = DIRECTIONS.PREFIX AND
                                                   DIRECTIONS.DIRECTION_ID = RATE_SCHEDULE.DIRECTION_ID AND TARIFFS.TARIFF_ID = RATE_SCHEDULE.TARIFF_ID AND
                                                   (CLIENTS_2.CLIENT_ID = CLIENTS.CLIENT_ID));
end;
/

prompt
prompt Creating procedure RECOUNT2
prompt ===========================
prompt
create or replace procedure billing."RECOUNT2"
is
begin
UPDATE    CLIENTS
SET              CLIENTS.MONEY = CLIENTS.MONEY - (SELECT     SUM(CONNECTIONS.DURATION * RATE_SCHEDULE.PRICE) AS EXPR1
                            FROM          NUMBERS, CLIENTS CLIENTS_2, CONNECTIONS, TARIFFS, DIRECTIONS, RATE_SCHEDULE
                            WHERE      NUMBERS.CLIENT_ID = CLIENTS_2.CLIENT_ID AND NUMBERS.NUMBER_ID = CONNECTIONS.NUMBER_ID AND
                                                   NUMBERS.TARIFF_ID = TARIFFS.TARIFF_ID AND CONNECTIONS.TARGET_PREFIX = DIRECTIONS.PREFIX AND
                                                   DIRECTIONS.DIRECTION_ID = RATE_SCHEDULE.DIRECTION_ID AND TARIFFS.TARIFF_ID = RATE_SCHEDULE.TARIFF_ID AND
                                                   (CLIENTS_2.CLIENT_ID = CLIENTS.CLIENT_ID));
INSERT INTO RECALCULATIONS
                      (ID, RECOUNT_DATE)
VALUES     (1, '12/12/2008');
end;
/

prompt
prompt Creating trigger CLIENTS_T1
prompt ===========================
prompt
CREATE OR REPLACE TRIGGER BILLING."CLIENTS_T1" 
BEFORE
insert on "CLIENTS"
for each row
begin

SELECT S_CLIENT_ID.NEXTVAL INTO :NEW.CLIENT_ID 
FROM DUAL;
end;
/

prompt
prompt Creating trigger CLIENTS_T2
prompt ===========================
prompt
CREATE OR REPLACE TRIGGER BILLING."CLIENTS_T2" 
BEFORE
insert on "CLIENTS"
for each row
begin
SELECT S_CLIENT_ACCOUNT.NEXTVAL INTO :NEW.account_number 
FROM DUAL;
end;
/

prompt
prompt Creating trigger CONNECTIONS_T1
prompt ===============================
prompt
CREATE OR REPLACE TRIGGER BILLING."CONNECTIONS_T1"
BEFORE
insert on "CONNECTIONS"
for each row
begin
SELECT S_CONNECTION_ID.NEXTVAL INTO :NEW.CONNECTION_ID 
FROM DUAL;

SELECT DISTINCT (:NEW.DURATION * RATE_SCHEDULE.PRICE)
INTO :NEW.COST
FROM RATE_SCHEDULE, DIRECTIONS, TARIFFS, NUMBERS, DUAL
WHERE RATE_SCHEDULE.DIRECTION_ID = DIRECTIONS.DIRECTION_ID AND RATE_SCHEDULE.TARIFF_ID = TARIFFS.TARIFF_ID AND 
TARIFFS.TARIFF_ID = NUMBERS.TARIFF_ID AND 
NUMBERS.NUMBER_ID = :NEW.NUMBER_ID AND
:NEW.TARGET_PREFIX = DIRECTIONS.PREFIX;
end;
/

prompt
prompt Creating trigger CONNECTIONS_T2
prompt ===============================
prompt
CREATE OR REPLACE TRIGGER BILLING."CONNECTIONS_T2"
BEFORE
insert on "CONNECTIONS"
for each row
begin
SELECT S_CONNECTION_ID.NEXTVAL INTO :NEW.CONNECTION_ID 
FROM DUAL;

SELECT DISTINCT (:NEW.DURATION * RATE_SCHEDULE.PRICE)
INTO :NEW.COST
FROM RATE_SCHEDULE, DIRECTIONS, TARIFFS, NUMBERS, DUAL
WHERE RATE_SCHEDULE.DIRECTION_ID = DIRECTIONS.DIRECTION_ID AND RATE_SCHEDULE.TARIFF_ID = TARIFFS.TARIFF_ID AND 
TARIFFS.TARIFF_ID = NUMBERS.TARIFF_ID AND 
NUMBERS.NUMBER_ID = :NEW.NUMBER_ID AND
:NEW.TARGET_PREFIX = DIRECTIONS.PREFIX;

UPDATE CLIENTS
SET CLIENTS.MONEY = CLIENTS.MONEY - :NEW.COST
WHERE CLIENTS.CLIENT_ID = 
(SELECT DISTINCT NUMBERS.CLIENT_ID 
FROM NUMBERS
WHERE NUMBERS.NUMBER_ID = :NEW.NUMBER_ID);
end;
/

prompt
prompt Creating trigger DIRECTIONS_T1
prompt ==============================
prompt
CREATE OR REPLACE TRIGGER BILLING."DIRECTIONS_T1" 
BEFORE
insert on "DIRECTIONS"
for each row
begin

SELECT S_DIRECTION_ID.NEXTVAL INTO :NEW.DIRECTION_ID 
FROM DUAL;
end;
/

prompt
prompt Creating trigger NUMBERS_T1
prompt ===========================
prompt
CREATE OR REPLACE TRIGGER BILLING."NUMBERS_T1" 
BEFORE
insert on "NUMBERS"
for each row
begin
SELECT S_NUMBER_ID.NEXTVAL INTO :NEW.NUMBER_ID 
FROM DUAL;
end;
/

prompt
prompt Creating trigger OPERATIONS_T1
prompt ==============================
prompt
CREATE OR REPLACE TRIGGER BILLING."OPERATIONS_T1" 
BEFORE
insert on "OPERATIONS"
for each row
begin
SELECT S_OPERATION_ID.NEXTVAL INTO :NEW.OPERATION_ID 
FROM DUAL;
end;
/

prompt
prompt Creating trigger OPERATIONS_T2
prompt ==============================
prompt
CREATE OR REPLACE TRIGGER BILLING."OPERATIONS_T2"
BEFORE
insert on "OPERATIONS"
for each row
begin
SELECT S_OPERATION_ID.NEXTVAL INTO :NEW.OPERATION_ID 
FROM DUAL;
SELECT     SYSDATE                            
INTO :NEW.OPERATION_DATE
FROM          DUAL;

end;
/

prompt
prompt Creating trigger TARIFFS_T1
prompt ===========================
prompt
CREATE OR REPLACE TRIGGER BILLING."TARIFFS_T1" 
BEFORE
insert on "TARIFFS"
for each row
begin
SELECT S_TARIFF_ID.NEXTVAL INTO :NEW.TARIFF_ID
FROM DUAL;
end;
/


spool off
